import { execute, select } from '../../utils/db';
import type { CourseTime } from '../../types/courseTime';
import { Page, PageReq } from '../../types/page';

export class CourseTimeDao {
  // 新增课程时间
  static async create(courseTime: Omit<CourseTime, 'id'>) {
    const sql = `INSERT INTO course_time (
      name, start_time, end_time
    ) VALUES (?, ?, ?)`;
    
    const params = [
      courseTime.name, 
      courseTime.start_time, 
      courseTime.end_time
    ];

    return execute(sql, params);
  }

  // 更新课程时间
  static async update(courseTime: Partial<CourseTime>) {
    const fields = [];
    const params = [];
    
    for (const [key, value] of Object.entries(courseTime)) {
      if (key !== 'id' && value !== undefined) {
        fields.push(`${key} = ?`);
        params.push(value);
      }
    }
    
    if (fields.length === 0) {
      return Promise.resolve();
    }
    
    params.push(courseTime.id);
    const sql = `UPDATE course_time SET ${fields.join(', ')} WHERE id = ?`;
    return execute(sql, params);
  }

  // 删除课程时间
  static async delete(id: number) {
    const sql = 'DELETE FROM course_time WHERE id = ?';
    return execute(sql, [id]);
  }

  // 根据ID查询课程时间
  static async findById(id: number): Promise<CourseTime | null> {
    const sql = 'SELECT * FROM course_time WHERE id = ?';
    const result = await select<CourseTime[]>(sql, [id]);
    return result.length > 0 ? result[0] : null;
  }

  // 查询所有课程时间
  static async list(): Promise<CourseTime[]> {
    const query = 'SELECT * FROM course_time';
    return await select<CourseTime[]>(query);
  }

  /**
   * 分页查询课程时间列表
   */
  static async page(params: PageReq<CourseTime>): Promise<Page<CourseTime>> {
    const { pageNum, pageSize } = params
    const offset = (pageNum - 1) * pageSize
    const fields = []
    const values = []
    const courseTime = params.params
    
    if (courseTime.name) {
      fields.push('name like ?')
      values.push(`%${courseTime.name}%`)
    }
    if (courseTime.start_time) {
      fields.push('start_time >= ?')
      values.push(courseTime.start_time)
    }
    if (courseTime.end_time) {
      fields.push('end_time <= ?')
      values.push(courseTime.end_time)
    }

    const condition = fields.length > 0 ? `WHERE ${fields.join(' AND ')}` : ''
    const countSql = `SELECT COUNT(*) as total FROM course_time ${condition}`
    const listSql = `SELECT * FROM course_time ${condition} LIMIT ${pageSize} OFFSET ${offset}`
    
    const totalResult = await select<{total: number}>(countSql, values)
    const listResult = await select<CourseTime[]>(listSql, values)
    
    return {
      total: totalResult[0].total || 0,
      list: listResult || []
    }
  }
}